#******************************************************************#
##author: v_sunao3
##create time: 2023-10-27 08:36:33
#******************************************************************#


# !/usr/bin/env python3
import sys
import time
from datetime import datetime
from pyhive import hive
import pymysql
import numpy as np

# 数据时间通过变量传入
yesterday = '${yyyy-MM-dd-1d}'
print("********************业务日期为：" + str(yesterday))
# 获取当前时间戳
current_timestamp = int(time.time())
# 获取当前时间
current_datetime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print("********************当前任务运行时间：" + str(current_datetime))
stat_date='${yyyyMMdd-1d}'

hive_database = '${hive_database}'
mysql_database = '${mysql_database}'
hive_table_prefix = '${hive_table_prefix}'

# 先确定该表要创建完成了
result_hive_table = f"{hive_table_prefix}python_mysql_cnt_df"


# 获取hive连接
def conn_to_hive(hive_database,
                 hive_host='172.25.1.64',
                 hive_port=7001,
                 hive_user='v_sunao3'
                 ):
    return hive.Connection(
        host=hive_host,
        port=hive_port,
        username=hive_user,
        database=hive_database
    )


# 连接mysql数据库
def conn_to_mysql(mysql_database,
                  mysql_host='172.25.1.58',
                  mysql_port=3306,
                  mysql_user='oceanus_user',
                  mysql_password='sqYcvrHc5gpEBH2a',
                  mysql_charset='utf8mb4',
                  ):
    return pymysql.connect(
        host=mysql_host,
        port=mysql_port,
        user=mysql_user,
        password=mysql_password,
        database=mysql_database,
        charset=mysql_charset
    )


def get_mysql_cnt(mysql_table, cursor_mysql):
    result = None
    try:
        # 获取mysql表的数据量
        cursor_mysql.execute(f"select count(*) from {mysql_database}.{mysql_table}")
        result = cursor_mysql.fetchone()[0]
    except Exception as e:
        print("=" * 100)
        print(f"Failed to connect to Mysql:{mysql_table} {str(e)}")
        print("=" * 100)
    finally:
        cursor_mysql.close()

    return result



def get_mysql_tables(cursor_mysql):
    try:
        # 获取mysql当前库的所有表
        cursor_mysql.execute("show tables")
        return cursor_mysql.fetchall()

    except Exception as e:
        print(f"Failed to connect to Mysql: {str(e)}")
        return None
    finally:
        cursor_mysql.close()


if __name__ == "__main__":


    # 链接数据库并创建游标
    conn_hive = conn_to_hive(hive_database=hive_database)
    conn_mysql = conn_to_mysql(mysql_database=mysql_database)

    mysql_tables = get_mysql_tables(conn_mysql.cursor())

    cursor_hive = conn_hive.cursor()
    try:
        # 检测当天任务是否执行
        cursor_hive.execute(f"""select stat_date from {hive_database}.{result_hive_table} where stat_date = '{stat_date}' """)
        if cursor_hive.fetchone() is not None: # 如果执行了
            # 删除当前分区数据;
            hive_init_sql = f"""alter table {hive_database}.{result_hive_table} drop partition (stat_date = '{stat_date}')"""
            cursor_hive.execute(hive_init_sql)

    except Exception as e:
        print("=" * 100)
        print(f"Failed to init hive partition:{result_hive_table} {str(e)}")
        print("=" * 100)
    finally:
        cursor_hive.close()

    table_count = len(list(mysql_tables))
    if mysql_tables is not None:
        for mysql_table in list(mysql_tables):
            hive_table = f'{hive_table_prefix}{mysql_table[0]}_df'
            mysql_cnt = get_mysql_cnt(mysql_table[0], conn_mysql.cursor())
            '''
            	`event_date` string  COMMENT "业务数据日期",
            	`mysql_database_name` string  COMMENT "mysql库名",
                `mysql_table_name` string  COMMENT "mysql表名",
                `mysql_cnt` bigint  COMMENT "mysql数据量",
                `cnt_record_time` string  COMMENT "脚本运行时间"
            '''
            # 插入数据
            cursor_hive = conn_hive.cursor()
            try:

                event_date,mysql_table_name,mysql_cnt,cnt_record_time = '${yyyyMMdd-1d}',mysql_table[0],mysql_cnt,current_datetime
                # 插入凌晨的mysql数据量快照
                sql = f"""INSERT INTO table {hive_database}.{result_hive_table} PARTITION(stat_date='{stat_date}') values
                ("{event_date}","{mysql_database}","{mysql_table_name}",{mysql_cnt},"{cnt_record_time}") """

                print("*"*50,"sql:",sql)
                cursor_hive.execute(sql)

            except Exception as e:
                print("=" * 100)
                print(f"Failed to init hive partition:{result_hive_table} {str(e)}")
                print("=" * 100)
            finally:
                cursor_hive.close()


    conn_mysql.close()
    conn_hive.close()
